/*++

Copyright (c) 2007  Microsoft Corporation 

Filename: 

    DBManager.cs 

Abstract: 

    This file contains definition of DataAccess Wrapper functions
     
--*/
# region References
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.IO;
using System.Security.Permissions;
using System.Text;
using UtilityManager;

[assembly: SecurityPermission(SecurityAction.RequestMinimum, Execution = true)]
[assembly: System.CLSCompliant(false)]
# endregion References

namespace DataManager
{
    /// <summary>
    /// DataAccess class is used to interact with data base.
    /// </summary>
    public class DBManager : IDisposable
    {
        #region Private Variables

        //Stores database related data
        private string connectionString;
        private SqlConnection sqlConnection;
        private bool disposed;

        #endregion Private Variables

        #region Constructor / Destructor

        /// <summary>
        /// Constructor of the class.
        /// </summary>
        public DBManager(string strConn)
        {
            try
            {
                // get connection string
                this.connectionString = strConn;
                this.sqlConnection = new SqlConnection(connectionString);

                // open the connection
                this.OpenConnection();
            }
            catch (SqlException)
            {
                throw;
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// Destructor for the class
        /// </summary>
        ~DBManager()
        {
            Dispose(false);
        }

        /// <summary>
        /// Dispose method
        /// </summary>
        public void Dispose()
        {
            Dispose(true);
        }

        /// <summary>
        /// Dispose method with option to supress finalization
        /// </summary>
        /// <param name="disposing">True to supress finalizarion</param>
        protected virtual void Dispose(bool disposing)
        {
            if (!disposed)
            {
                // Dispose of resources held by this instance.
                this.CloseConnection();

                // Set the sentinel.
                disposed = true;

                // Suppress finalization of this disposed instance.
                if (disposing)
                {
                    GC.SuppressFinalize(this);
                }
            }
        }

        #endregion Constructor

        #region Methods

        /// <summary>
        /// This method execute StoreProceedure and on successful it returns dataset.
        /// and if any exception occurs it throws new exception
        /// </summary>
        /// <param name="cmdDataAccess">Sql Command object to execute SP.</param>
        /// <returns> It ruturns DataSet</returns>
        public DataSet GetDataSet(SqlCommand cmdDataAccess)
        {
            SqlDataAdapter sdaDataAccess = null;
            DataSet dsDataAccess = new DataSet();
            dsDataAccess.Locale = CultureInfo.InvariantCulture;

            try
            {
                // Create Connection
                this.OpenConnection();

                if (cmdDataAccess == null)
                {
                    throw new ArgumentNullException(string.Empty);
                }

                cmdDataAccess.Connection = this.sqlConnection;
                cmdDataAccess.CommandTimeout = 300;
                sdaDataAccess = new SqlDataAdapter(cmdDataAccess);
                sdaDataAccess.Fill(dsDataAccess, "DataAccess");
                cmdDataAccess.Parameters.Clear();

                // Close Connection
                this.CloseConnection();
            }
            catch (SqlException sq)
            {
                if (sq.Message.Contains("Invalid object name"))
                {
                    //throw new Exception(Global.GetResourceString("InvalidSCOMServerOrDBName"));
                    throw sq;
                }
                else 
                    throw;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                // close the connection
                this.CloseConnection();
            }
            return dsDataAccess;
        }

        /// <summary>
        /// This method execute StoreProceedure and on successfull it returns true value
        ///  and if any exception occurs it throws new exception
        /// </summary>
        /// <param name="cmdDataAccess">Sql Command object to execute SP.</param>
        /// <returns>on successfull it returns true value</returns>
        public string ExecuteQuery(SqlCommand cmdDataAccess)
        {
            string sReturn = string.Empty;
            try
            {
                // Create Connection
                this.OpenConnection();

                if (cmdDataAccess == null)
                {
                    throw new ArgumentNullException(sReturn);
                }
                cmdDataAccess.CommandType = CommandType.Text;
                cmdDataAccess.Connection = this.sqlConnection;
                cmdDataAccess.CommandTimeout = 300;
                sReturn = Convert.ToString(cmdDataAccess.ExecuteNonQuery(), CultureInfo.InvariantCulture);
            }
            catch (SqlException)
            {
                throw;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                // close the connection
                this.CloseConnection();
            }
            return sReturn;
        }

        /// <summary>
        /// Opens the database connection taking care of auto 
        /// connection management
        /// </summary>
        public void OpenConnection()
        {
            // if Connection object is null then instantiate it, if not 
            // check its state
            if (this.sqlConnection != null)
            {
                // If connection state is not open then close it
                if (this.sqlConnection.State != ConnectionState.Open)
                {
                    // Open a connection
                    this.sqlConnection.Open();
                }
            }
            else
            {
                // Instantiating Connection object.
                this.sqlConnection = new SqlConnection(this.connectionString);

                // Opening Database Connection.
                this.sqlConnection.Open();
            }
        }

        /// <summary>
        /// Closes database connection.
        /// </summary>
        public void CloseConnection()
        {
            // Check whether connection object is null or not, if it is 
            // then instantiate it.
            if (this.sqlConnection != null)
            {
                // If the state of the connection is not 'Closed' 
                // then close it.
                if (this.sqlConnection.State != ConnectionState.Closed)
                {
                    // Close the connection
                    this.sqlConnection.Close();
                }

                this.sqlConnection = null;
            }
        }

        /// <summary>
        /// Create Stored Proc to fetch data 
        /// </summary>
        /// <param name="objDbManager"></param>
        /// <param name="fs"></param>
        public static void CreateStoredProc(DBManager objDBManager, Stream fs)
        {
            SqlCommand cmdData = new SqlCommand();
            ArrayList sqlLines = null;

            if (fs == null)
            {
                return;
            }

            if (objDBManager == null)
            {
                return;
            }

            sqlLines = GetSql(fs);

            foreach (string sqlLine in sqlLines)
            {
                if (sqlLine.Length > 0)
                {
                    try
                    {
                        cmdData.CommandType = CommandType.Text;
                        cmdData.CommandText = sqlLine;
                        objDBManager.ExecuteQuery(cmdData);
                    }
                    catch (System.Threading.ThreadAbortException)
                    {
                        // Ignore thread abort exception
                        // return true;
                    }
                    catch (RackException ex)
                    {
                        // Exception from sub function. Pass on to entry point
                        throw new RackException("Create Stored Proc", ex.InnerException);
                    }
                    catch (Exception ex)
                    {
                        // Exception from current function
                        throw new RackException("Create Stored Proc", ex);
                    }
                }
            }
        }

        /// <summary>
        /// Function to get the array list by sending the sql file name		
        /// </summary>
        /// <param name="databaseName">database name</param>
        /// <param name="serverName">server name</param>
        /// <param name="fileName">file name</param>		
        private static ArrayList GetSql(Stream sqlQueryStream)
        {
            // Get the current assembly.
            String sqlLine = null;
            StringBuilder sqlQuery = null;
            ArrayList sqlArray = null;

            try
            {
                sqlQuery = new StringBuilder();
                sqlArray = new ArrayList();
                // Resources are named using a fully qualified name				
                StreamReader reader = new StreamReader(sqlQueryStream);

                // Read the resource script to split queries
                while ((sqlLine = reader.ReadLine()) != null)
                {
                    if (String.Compare(sqlLine.Trim().ToUpper(CultureInfo.CurrentCulture), "GO") == 0)
                    {
                        sqlArray.Add(sqlQuery.ToString());
                        sqlQuery.Length = 0;
                    }
                    else if (sqlLine.Trim().Length > 0)
                    {
                        sqlQuery.Append(sqlLine + "\n");

                    }
                }

                return sqlArray;
            }

            catch (System.Threading.ThreadAbortException)
            {
                // Ignore thread abort exception
                return null;
            }
            catch (RackException ex)
            {
                // Exception from sub function. Pass on to entry point
                throw new RackException("GetSql", ex.InnerException);
            }
            catch (Exception ex)
            {
                // Exception from current function
                throw new RackException("GetSql", ex);
            }
            finally
            {
                sqlArray = null;
                sqlQuery = null;
            }
        }

        #endregion Method
    }
}
